import qrcode
import pathlib
from pyzbar.pyzbar import decode
import requests
import json
import sqlite3
import logging
import os
from openpyxl import Workbook
from PIL import Image
db=None
cur=None
basepath=pathlib.Path(__file__).parent
def getID(text):
    data=text.split("data_source=\"contral_")
    list=[]
    for i in data :
        list.append(i[0:34])
    del list[0]
    return list

def existID(id):
    global db
    global cur    
    sql="select id from log where id='{id}'".format(id=id)
    r=executeSql(sql)
    if(r.fetchone()==None):
        return False
    else:
        return True

def executeSql(sql):
    global db
    global cur
    try:
        cur.execute(sql)
    except :
        logging.error(sql)
        db.rollback()
    else:
        db.commit()
        return cur

def initdb():
    global db
    global cur
    db = sqlite3.connect(str(basepath/"log.db"), check_same_thread=False)
    cur = db.cursor()
    cur.execute("select name from sqlite_master where type='table' and name='log' order by name")
    if(len(cur.fetchall())==0):
        sql=r'CREATE TABLE log (  "id" text NOT NULL,   "name" TEXT,   "class" TEXT,   "cardid" text,   "note" TEXT,   "time" TEXT,   PRIMARY KEY ("id") );'
        executeSql(sql)
    
initdb()

for root, dirs, files in os.walk(basepath):
    for name in files:
        path=pathlib.Path(name)
        if(path.suffix==".png" or path.suffix==".jpg"):
            img=Image.open(str(basepath / path))
            result = decode(img)
            text=result[0][0].decode("utf-8")
            id=text[59:]
            if(not existID(id)):
                url=r"https://f.10086.cn/wxqyh/open/openFormAction!detailGetFroms.action?id="+id
                r=requests.get(url)
                js=json.loads(r.text)
                list=getID(js['data']['definitions'])
                info=[]
                for i in list:
                    try:
                        info.append(js['data']['itemMap'][i])
                        print(js['data']['itemMap'][i])
                    except:
                        info.append("")
                print(url)
                sql=r"INSERT INTO log(id, name, class, cardid, note, time ) VALUES ('{id}', '{name}', '{clas}', '{cardid}', '{note}', '{time}')".format(
                    id=id,
                    name=info[0],
                    clas=info[2],
                    cardid=info[3],
                    note=info[11],
                    time=js['data']['detail']['createaTime']
                )
                executeSql(sql)
            else:
                sql=r"select id , name from log where id='{id}'".format(id=id)
                r=executeSql(sql)
                print(r.fetchone())

wb = Workbook()
mySheet = wb.create_sheet(index=0, title="Mysheet")

sql="select name,class,cardid,note,time from log"
title=['名字','班级','身份证号码','原因','时间']
ii=2
ji=1
for i in title:
    mySheet.cell(row=1, column=ji, value=i)
    ji+=1
r=executeSql(sql)

for i in r:
    print(i)
    ji=1
    for j in i:
        mySheet.cell(row=ii, column=ji, value=j)
        ji+=1
    ii+=1

path = basepath/'log.xlsx'
wb.save(basepath/'log.xlsx')
exit=input("press enter to exit")






